package ru.arriah.servicedesk.ejb;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;

import javax.ejb.EJBException;
import javax.ejb.SessionContext;
import javax.sql.DataSource;

import ru.arriah.common.ejb.GenericSessionBean;
import ru.arriah.servicedesk.bean.ClientBean;
import ru.arriah.servicedesk.bean.ClientTypeBean;
import ru.arriah.servicedesk.bean.IntWrapper;
import ru.arriah.servicedesk.help.DbUtils;
import ru.arriah.servicedesk.help.Utils;

public class ClientManager extends GenericSessionBean {
   protected DataSource dataSource;

   enum ClientOrderByEnum {
     N,
     name, 
     type, 
     tel, 
     email, 
     www
   }; 
   
   
   public ClientManager() {
      super();
      dataSource = null;
   }

   
   public void setSessionContext(
      SessionContext sessionContext
   ) throws EJBException, RemoteException {
      super.setSessionContext(sessionContext);

      try {
         this.dataSource = Utils.getDataSource();         
      } catch (Exception e) {
         handleException(e, "setSessionContext");
         throw new EJBException(e);
      }
   }
   
   
   public int authorize(String login, String password) {
     
      Connection connection = null;
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     int id;     
     try {       
       connection = dataSource.getConnection();       
       preparedStatement = connection.prepareStatement(
           "SELECT `user`.`id`, `user`.`password` FROM" +
           " `user`" +           
           " WHERE `user`.`status` = 'active'" +
           " AND `user`.`user_type` = 'client'" +           
           " AND `user`.`login` = ?"
       );
       
       preparedStatement.setString(1, login);
       
       resultSet = preparedStatement.executeQuery();              
       
       if (resultSet.next()) {
         if(!resultSet.getString("password").equals(password)) {
           throw new IllegalArgumentException("Password is incorrect");
         } else 
         id = resultSet.getInt("id");
       } else throw new IllegalArgumentException("Password is incorrect");
                     
       return id;
     } catch (Exception e) {
       handleException(e, "authorize");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(connection, preparedStatement, resultSet);
    }
   }
   
   
   
   private ClientBean resultSetToBean(ResultSet resultSet) {
    try {
      ClientBean clientBean = new ClientBean();

      clientBean.setId(resultSet.getInt("user.id"));
      clientBean.setOrganizationId(resultSet.getInt("organization_id"));         
      clientBean.setClientTypeId(resultSet.getInt("client_settings.clienttype_id"));
      clientBean.setDepartmentId(resultSet.getInt("department_id"));
      clientBean.setAddress(resultSet.getString("address"));
      clientBean.setNotes(resultSet.getString("notes"));
      clientBean.setEmail1(resultSet.getString("user.email"));
      clientBean.setEmail2(resultSet.getString("email1"));
      clientBean.setEmail3(resultSet.getString("email2"));
      clientBean.setIcq(resultSet.getString("icq"));
      clientBean.setTel1(resultSet.getString("tel1"));
      clientBean.setTel2(resultSet.getString("tel2"));
      clientBean.setTel3(resultSet.getString("tel3"));
      clientBean.setFirstName(resultSet.getString("user.firstName"));
      clientBean.setLastName(resultSet.getString("user.lastName"));
      clientBean.setMiddleName(resultSet.getString("user.middleName"));
      clientBean.setWww(resultSet.getString("www"));
      clientBean.setPosition(resultSet.getString("position"));
      clientBean.setPassword(resultSet.getString("password"));
      clientBean.setLogin(resultSet.getString("login"));
      clientBean.setBirthday(resultSet.getDate("birthday"));
      clientBean.setStatus(resultSet.getString("user.status"));

      return clientBean;
    } catch (Exception e) {
      handleException(e, "resultSetToBean");
      throw new EJBException(e);
    }
  }
   
   
   
   private Collection<ClientBean> resultSetToCollection(ResultSet resultSet) {     
     try {      
       ArrayList<ClientBean> requestsList = new ArrayList<ClientBean>();      
       while(resultSet.next()) {
         requestsList.add(resultSetToBean(resultSet));  
       }
       return requestsList;
     } catch (Exception e) {
       handleException(e, "resultSetToCollection");
       throw new EJBException(e);
     }
   }  

   
   
   private Collection<ClientBean> resultSetToCollection(
     ResultSet resultSet,
     int page,
     int itemsPerPage,
     IntWrapper records
   ) {     
     try {                  
       ArrayList<ClientBean> requestsList = 
          new ArrayList<ClientBean>();
       
       if (!resultSet.next())
         return requestsList;
       
       int itemCount = itemsPerPage;
              
       resultSet.last();              
       records.setValue(resultSet.getRow());       
       int pages = (records.getValue() / itemCount);       
       if(records.getValue() % itemCount != 0) {
          pages++;
       }       

       resultSet.absolute(page * itemCount + 1);
       
       int i = 0;
       while(i < itemCount && !resultSet.isAfterLast()) {
         requestsList.add(resultSetToBean(resultSet));
         resultSet.next();
         i++;
       }                                                
       
       return requestsList;
     } catch (Exception e) {
       handleException(e, "resultSetToCollection");
       throw new EJBException(e);
     }
   }
   
   
   
   public ClientBean selectClientByLogin(String login) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     try {       
       connection = dataSource.getConnection();
       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM"+
           " `user`,"+
           " `client_settings`, " +
           " `clienttype`" +
           " WHERE `user`.`id` = `client_settings`.`user_id`" +
           " AND `client_settings`.`clienttype_id` = `clienttype`.`id`" +           
           " AND `user`.`user_type` = 'client'" +
           " AND `user`.`login` = ?" 
       );
       
       preparedStatement.setString(1, login);
       resultSet = preparedStatement.executeQuery();
       
       ClientBean clientBean = null;
       if (resultSet.next()) 
         clientBean = this.resultSetToBean(resultSet);              
       
       return clientBean;
     } catch (Exception e) {
       handleException(e, "selectClientByLogin");
       throw new EJBException(e);         
    }   finally {
       DbUtils.closeQuietly(connection, preparedStatement, resultSet);
    }
   }
   
   
   /**
    * Return user password by id
    * @param id
    * @return
    */
   public String getUserPassword(int id) {
      
      Connection connection = null;     
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;
      String password = null;
      
      try {       
        connection = dataSource.getConnection();
        
        preparedStatement = connection.prepareStatement(
            "SELECT `password` FROM"+
            " `user`"+
            " WHERE `user`.`id` = ?" 
        );
        
        preparedStatement.setInt(1, id);
        resultSet = preparedStatement.executeQuery();
        
        
        if (resultSet.next()) 
           password = resultSet.getString("password");
        
        return password;
      } catch (Exception e) {
        handleException(e, "getUserPassword");
        throw new EJBException(e);         
     }   finally {
        DbUtils.closeQuietly(connection, preparedStatement, resultSet);
     }
    }
  

   
   /**
    * select clients
    * @return clients sorted by lastname
    */  
   public Collection<ClientBean> selectClients() {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     try {       
       connection = dataSource.getConnection();
       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM `user`, `client_settings`, `clienttype`" +
           " WHERE `user`.`id` = `client_settings`.`user_id`" +
           " AND `client_settings`.`clienttype_id` = `clienttype`.`id`" +
           " AND `user`.`status` = 'active'" +
           " AND `user`.`user_type` = 'client'" +
           " ORDER BY `user`.`lastname` ASC"
       );
       
       resultSet = preparedStatement.executeQuery();       
       Collection<ClientBean> clientsList = this.resultSetToCollection(resultSet);                    
       return clientsList;
       
     } catch (Exception e) {
       handleException(e, "selectClients");
       throw new EJBException(e);       
    }  finally {
       DbUtils.closeQuietly(connection, preparedStatement, resultSet);
    }
   }
   
   
   
   public Collection<ClientBean> selectClients(
      String orderBy, 
      String order,
      int page, 
      int itemsPerPage, 
      IntWrapper records
   ) {
      return this.selectClients(
         orderBy, 
         order, 
         page, 
         itemsPerPage, 
         records,
         "'active'"
      );
   }
   
   
   
   public Collection<ClientBean> selectClients(
     String orderBy, 
     String order,
     int page,
     int itemsPerPage,
     IntWrapper records,
     String status
   ) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     try {       
       connection = dataSource.getConnection();
       
       order = (order.equals("ASC") ? "ASC" : "DESC");
       
       try {         
         ClientOrderByEnum orderEnum = ClientOrderByEnum.valueOf(orderBy);
         switch (orderEnum) {
         case N:
           orderBy = "`user`.`id`";
           break;
         case name:
           orderBy = "`user`.`lastname`";
           break;
         case tel:
           orderBy = "`client_settings`.`tel1`";
           break;
         case email:  
           orderBy = "`user`.`email`";
           break;                    
         case www:
           orderBy = "`client_settings`.`www`";
           break;
         default:
           orderBy = "`user`.`id`";
           break;
         }
       } catch (Exception e) {
         orderBy = "`user`.`id`";
       }
       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM `user`, `client_settings`, `clienttype`" +
           " WHERE `user`.`id` = `client_settings`.`user_id`" +
           " AND `client_settings`.`clienttype_id` = `clienttype`.`id`" +
           " AND `user`.`status` IN ("+ status +")" +
           " AND `user`.`user_type` = 'client'" +
           " ORDER BY "+orderBy+" "+order           
       );
       
       resultSet = preparedStatement.executeQuery();
       
       Collection<ClientBean> clientsList = 
         this.resultSetToCollection(
           resultSet,
           page,
           itemsPerPage,
           records
         );              
       
       return clientsList;
     } catch (Exception e) {
       handleException(e, "selectClients");
       throw new EJBException(e);         
    } finally {
       DbUtils.closeQuietly(
          connection, 
          preparedStatement, 
          resultSet
       );
    }     
   }
   
   
   
   /**
    * Select client by organization sorted by lastname
    * @param organizationId
    * @return
    */
   public Collection<ClientBean> selectClientsByOrganization(
     int organizationId
   ) {
     Connection connection = null;     
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     try {       
       connection = dataSource.getConnection();
       
       preparedStatement = connection.prepareStatement(
           "SELECT * FROM `user`, `client_settings`, `clienttype`" +
           " WHERE `user`.`id` = `client_settings`.`user_id`" +
           " AND `client_settings`.`clienttype_id` = `clienttype`.`id`" +
           " AND `user`.`status` = 'active'" +
           " AND `user`.`user_type` = 'client'" +
           " AND `client_settings`.`organization_id` = ?" +
           " ORDER BY `user`.`lastname` ASC" 
       );
       
       preparedStatement.setInt(1, organizationId);
       resultSet = preparedStatement.executeQuery();
       
       Collection<ClientBean> clientsList = 
         this.resultSetToCollection(resultSet);              
       
       return clientsList;
     } catch (Exception e) {
       handleException(e, "selectClientsByOrganization");
       throw new EJBException(e);         
    }  finally {
       DbUtils.closeQuietly(connection, preparedStatement, resultSet);
    }
   }
   
   
   
   /**
    * select clients by department sorted by lastname
    * @param departmentId
    * @return
    */
   public Collection<ClientBean> selectClientsByDepartment(int departmentId) {

      Connection connection = null;
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;

      try {
         connection = dataSource.getConnection();

         preparedStatement = connection.prepareStatement(
            "SELECT * FROM `user`, `client_settings`, `clienttype`" + 
            " WHERE `user`.`id` = `client_settings`.`user_id`" + 
            " AND `client_settings`.`clienttype_id` = `clienttype`.`id`" + 
            " AND `user`.`status` = 'active'" + 
            " AND `user`.`user_type` = 'client'" + 
            " AND `client_settings`.`department_id` = ?" + 
            " ORDER BY `user`.`lastname` ASC"
         );

         preparedStatement.setInt(1, departmentId);
         resultSet = preparedStatement.executeQuery();

         Collection<ClientBean> clientsList = this
            .resultSetToCollection(resultSet);

         return clientsList;
      } catch (Exception e) {
         handleException(e, "selectClientsByDepartment");
         throw new EJBException(e);
      } finally {
         DbUtils.closeQuietly(connection, preparedStatement, resultSet);
      }
   }
   

   /**
    * set inactive status
    * @param id
    * @return
    */
   public boolean deleteClient(int id) {
      return this.setClientStatus(id, "inactive");
   }
   
   
   
   /**
    * set active status
    * @param id
    * @return
    */
   public boolean restoreClient(int id) {
      return this.setClientStatus(id, "active");
   }
   
   
   
   private boolean setClientStatus(int id, String status) {
      
      Connection connection = null;
      PreparedStatement preparedStatement = null;
      
      try {
         connection = dataSource.getConnection();
         preparedStatement = 
            connection.prepareStatement(
               "UPDATE `user` SET `status` = ?" + 
               " WHERE `id` = ?"
            );

         preparedStatement.setString(1, status);
         preparedStatement.setInt(2, id);         
         
         int result = preparedStatement.executeUpdate();

         return (result != 0);
      } catch (Exception e) {
         handleException(e, "setClientStatus");
         throw new EJBException(e);
      } finally {
         DbUtils.closeQuietly(
            connection, 
            preparedStatement
         );
      }
   }
   
   
   
   public Collection<ClientTypeBean> selectClientTypes() {
      
      Connection connection = null;
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;
      
      try {
         ArrayList<ClientTypeBean> clientTypesList = new ArrayList<ClientTypeBean>();

         connection = dataSource.getConnection();
         preparedStatement = 
            connection.prepareStatement(
               "SELECT * FROM `clienttype`"
            );

         resultSet = preparedStatement.executeQuery();

         while (resultSet.next()) {
            ClientTypeBean clientTypeBean = new ClientTypeBean();

            clientTypeBean.setId(resultSet.getInt("id"));
            clientTypeBean.setType(resultSet.getString("clienttype_name"));
            clientTypesList.add(clientTypeBean);
         }

         return clientTypesList;
      } catch (Exception e) {
         handleException(e, "selectClientTypes");
         throw new EJBException(e);
      } finally {
         DbUtils.closeQuietly(
            connection, 
            preparedStatement, 
            resultSet
         );
      }
   }
   
   
   
   public boolean addUser() {
     return true;
   }
   
   

   public boolean addClient(ClientBean clientBean) {
     Connection connection = null;
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     
     try {                 
       int id;
       int result = 0;          
         
       connection = dataSource.getConnection();       
       
       preparedStatement = connection.prepareStatement(
           "INSERT INTO `user`" +
           " SET `firstname` = ?," +           
           " `lastname` = ?," +
           " `middlename` = ?," +
           " `login` = ?," +
           " `password` = ?," +
           " `email` = ?," +
           " `user_type` = 'client'"
       );
       
       preparedStatement.setString(1, clientBean.getFirstName());
       preparedStatement.setString(2, clientBean.getLastName());
       preparedStatement.setString(3, clientBean.getMiddleName());
       preparedStatement.setString(4, clientBean.getLogin());
       preparedStatement.setString(5, clientBean.getPassword());
       preparedStatement.setString(6, clientBean.getEmail1());
       
       preparedStatement.executeUpdate();
       
       resultSet = preparedStatement.getGeneratedKeys();
       
       if(resultSet.next()) {                  
         preparedStatement = connection.prepareStatement(
             "INSERT INTO `client_settings`" +
             " SET `user_id` = ?," +
             " `position` = ?," +             
             " `tel1` = ?," +
             " `tel2` = ?," +           
             " `tel3` = ?," +
             " `email1` = ?," +
             " `email2` = ?," +
             " `www` = ?," +
             " `icq` = ?," +
             " `address` = ?," +
             " `notes` = ?," +
             " `clienttype_id` = ?," +
             " `organization_id` = ?," +
             " `department_id` = ?," +
             " `birthday` = ?"
         );             
         
         preparedStatement.setInt(1, resultSet.getInt(1));
         preparedStatement.setString(2, clientBean.getPosition());
         preparedStatement.setString(3, clientBean.getTel1());
         preparedStatement.setString(4, clientBean.getTel2());
         preparedStatement.setString(5, clientBean.getTel3());         
         preparedStatement.setString(6, clientBean.getEmail2());
         preparedStatement.setString(7, clientBean.getEmail3());
         preparedStatement.setString(8, clientBean.getWww());
         preparedStatement.setString(9, clientBean.getIcq());
         preparedStatement.setString(10, clientBean.getAddress());
         preparedStatement.setString(11, clientBean.getNotes());

         preparedStatement.setInt(12, clientBean.getClientTypeId());                  
         preparedStatement.setInt(13, clientBean.getOrganizationId());
         
         id = clientBean.getDepartmentId();
         if (id == 0)
           preparedStatement.setNull(14, Types.INTEGER);
         else preparedStatement.setInt(14, id);                  
                  
         if (clientBean.getBirthday() != null) {
           preparedStatement.setDate(15, new java.sql.Date(clientBean.getBirthday().getTime()));
         } else 
           preparedStatement.setNull(15, Types.DATE);
         
         result = preparedStatement.executeUpdate();    
       }       
                     
       return (result!=0);
     } catch (Exception e) {
       handleException(e, "addClient");
       throw new EJBException(e);         
    }  finally {
       DbUtils.closeQuietly(
          connection, 
          preparedStatement, 
          resultSet
       );
    }
   }
   
   
   
   public boolean updateClient(ClientBean clientBean) {
      
      Connection connection = null;
      PreparedStatement preparedStatement = null;

      int id;

      try {
         connection = dataSource.getConnection();

         preparedStatement = connection.prepareStatement(
            "UPDATE `user`" + 
            " SET `firstname` = ?," + 
            " `lastname` = ?," + 
            " `middlename` = ?," + 
            " `login` = ?," + 
            " `password` = ?," + 
            " `email` = ?," + 
            " `user_type` = 'client'" + 
            " WHERE `user`.`id` = ?"
         );

         preparedStatement.setString(1, clientBean.getFirstName());
         preparedStatement.setString(2, clientBean.getLastName());
         preparedStatement.setString(3, clientBean.getMiddleName());
         preparedStatement.setString(4, clientBean.getLogin());
         preparedStatement.setString(5, clientBean.getPassword());
         preparedStatement.setString(6, clientBean.getEmail1());
         preparedStatement.setInt(7, clientBean.getId());

         int result = preparedStatement.executeUpdate();

         if (result != 0) {
            preparedStatement = connection.prepareStatement(
               "UPDATE `client_settings`" + 
               " SET `department_id` = ?," + 
               " `position` = ?," + 
               " `tel1` = ?," + 
               " `tel2` = ?," + 
               " `tel3` = ?," + 
               " `email1` = ?," + 
               " `email2` = ?," + 
               " `www` = ?," + 
               " `icq` = ?," + 
               " `address` = ?," + 
               " `notes` = ?," + 
               " `clienttype_id` = ?," + 
               " `organization_id` = ?," + 
               " `birthday` = ?" + 
               " WHERE `client_settings`.`user_id` = ?"
            );

            preparedStatement.setInt(1, clientBean.getDepartmentId());
            preparedStatement.setString(2, clientBean.getPosition());
            preparedStatement.setString(3, clientBean.getTel1());
            preparedStatement.setString(4, clientBean.getTel2());
            preparedStatement.setString(5, clientBean.getTel3());
            preparedStatement.setString(6, clientBean.getEmail2());
            preparedStatement.setString(7, clientBean.getEmail3());
            preparedStatement.setString(8, clientBean.getWww());
            preparedStatement.setString(9, clientBean.getIcq());
            preparedStatement.setString(10, clientBean.getAddress());
            preparedStatement.setString(11, clientBean.getNotes());

            id = clientBean.getClientTypeId();
            if (id == 0)
               preparedStatement.setNull(12, Types.INTEGER);
            else
               preparedStatement.setInt(12, id);

            id = clientBean.getOrganizationId();
            if (id == 0)
               preparedStatement.setNull(13, Types.INTEGER);
            else
               preparedStatement.setInt(13, id);

            if (clientBean.getBirthday() != null) {
               preparedStatement.setDate(14, new java.sql.Date(clientBean
                  .getBirthday().getTime()));
            } else
               preparedStatement.setNull(14, Types.DATE);

            preparedStatement.setInt(15, clientBean.getId());

            preparedStatement.executeUpdate();
         }

         return true;
      } catch (Exception e) {
         handleException(e, "updateClient");
         throw new EJBException(e);
      } finally {
         DbUtils.closeQuietly(
            connection, 
            preparedStatement
         );
      }
   }
   
   
   /**
    * Equals to updateClient, without password update
    * @param clientBean
    * @return
    */
   public boolean updateClientByDispatcher(ClientBean clientBean) {
      
      Connection connection = null;
      PreparedStatement preparedStatement = null;

      int id;

      try {
         connection = dataSource.getConnection();

         preparedStatement = connection.prepareStatement(
            "UPDATE `user`" + 
            " SET `firstname` = ?," + 
            " `lastname` = ?," + 
            " `middlename` = ?," + 
            " `login` = ?," + 
            //" `password` = ?," + 
            " `email` = ?," + 
            " `user_type` = 'client'" + 
            " WHERE `user`.`id` = ?"
         );

         preparedStatement.setString(1, clientBean.getFirstName());
         preparedStatement.setString(2, clientBean.getLastName());
         preparedStatement.setString(3, clientBean.getMiddleName());
         preparedStatement.setString(4, clientBean.getLogin());
         //preparedStatement.setString(5, clientBean.getPassword());
         preparedStatement.setString(5, clientBean.getEmail1());
         preparedStatement.setInt(6, clientBean.getId());

         int result = preparedStatement.executeUpdate();

         if (result != 0) {
            preparedStatement = connection.prepareStatement(
               "UPDATE `client_settings`" + 
               " SET `department_id` = ?," + 
               " `position` = ?," + 
               " `tel1` = ?," + 
               " `tel2` = ?," + 
               " `tel3` = ?," + 
               " `email1` = ?," + 
               " `email2` = ?," + 
               " `www` = ?," + 
               " `icq` = ?," + 
               " `address` = ?," + 
               " `notes` = ?," + 
               " `clienttype_id` = ?," + 
               " `organization_id` = ?," + 
               " `birthday` = ?" + 
               " WHERE `client_settings`.`user_id` = ?"
            );

            preparedStatement.setInt(1, clientBean.getDepartmentId());
            preparedStatement.setString(2, clientBean.getPosition());
            preparedStatement.setString(3, clientBean.getTel1());
            preparedStatement.setString(4, clientBean.getTel2());
            preparedStatement.setString(5, clientBean.getTel3());
            preparedStatement.setString(6, clientBean.getEmail2());
            preparedStatement.setString(7, clientBean.getEmail3());
            preparedStatement.setString(8, clientBean.getWww());
            preparedStatement.setString(9, clientBean.getIcq());
            preparedStatement.setString(10, clientBean.getAddress());
            preparedStatement.setString(11, clientBean.getNotes());

            id = clientBean.getClientTypeId();
            if (id == 0)
               preparedStatement.setNull(12, Types.INTEGER);
            else
               preparedStatement.setInt(12, id);

            id = clientBean.getOrganizationId();
            if (id == 0)
               preparedStatement.setNull(13, Types.INTEGER);
            else
               preparedStatement.setInt(13, id);

            if (clientBean.getBirthday() != null) {
               preparedStatement.setDate(14, new java.sql.Date(clientBean
                  .getBirthday().getTime()));
            } else
               preparedStatement.setNull(14, Types.DATE);

            preparedStatement.setInt(15, clientBean.getId());

            preparedStatement.executeUpdate();
         }

         return true;
      } catch (Exception e) {
         handleException(e, "updateClientByDispatcher");
         throw new EJBException(e);
      } finally {
         DbUtils.closeQuietly(
            connection, 
            preparedStatement
         );
      }
   }
   

   /**
    * select client by id
    * 
    * @param clientId
    * @return
    */
   public ClientBean selectClient(int clientId) {
      Connection connection = null;
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;
      
      try {
         ClientBean clientBean = new ClientBean();
         connection = dataSource.getConnection();

         preparedStatement = connection.prepareStatement(
            "SELECT * FROM `user`, `client_settings`, `clienttype`" + 
            " WHERE `user`.`id` = `client_settings`.`user_id`" + 
            " AND `client_settings`.`clienttype_id` = `clienttype`.`id`" + 
            " AND `user`.`status` = 'active'" + 
            " AND `user`.`user_type` = 'client'" + 
            " AND `user`.`id` = ?"
         );

         preparedStatement.setInt(1, clientId);
         resultSet = preparedStatement.executeQuery();

         if (resultSet.next()) {
            clientBean = resultSetToBean(resultSet);
         }

         return clientBean;
      } catch (Exception e) {
         handleException(e, "selectClient");
         throw new EJBException(e);
      } finally {
         DbUtils.closeQuietly(
            connection, 
            preparedStatement, 
            resultSet
         );
      }
   }
     
}